﻿/******************************************************************
** 文 件 名: Bu_DocReceive_DAL.cs
** Copyright (c) 2010-2012 Honestysoft (GuangZhou) Co.,LTD
** 创 建 人: 此代码由生成器自动生成
** 创建日期: 2012-3-21 1:28:04
** 描    述: 实现公共部分的数据访问封装，提供资料接收表的数据访问类
** 版    本: 2.0.0.0
** 修改描述:
** 修 改 人:
** 修改日期:
******************************************************************/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using Honestysoft.BaseObject;
using Honestysoft.Financing.Model;

namespace Honestysoft.Financing.DataAccess
{
	/// <summary>
    /// Bu_DocReceive数据访问类
    /// </summary>
	public partial class Bu_DocReceive_DAL : BaseDataAccess<Bu_DocReceive>
    {		
		/// <summary>
        /// 构造函数，使用指定的连接字符串
        /// </summary>
        /// <param name="connectionStringName">连接字符串名称</param>
        public Bu_DocReceive_DAL(string connectionStringName)
            : base(connectionStringName)
        {

        }

        public string GetNextCode()
        {
            string sql = @"Select Max(A.Code) From (
Select Code From Bu_DocReceive Where Year(ReceiveDate)=Year(GetDate())
Union
Select Code From Bu_DocReceive_Temp Where Year(ReceiveDate)=Year(GetDate())) A";
            object obj = ExecuteScalar(sql);
            if (obj == null || obj == DBNull.Value)
            {
                return DateTime.Today.Year.ToString() + "-0001";
            }
            else
            {
                string par = obj.ToString();
                par = par.Substring(5);
                return DateTime.Today.Year.ToString() + "-" + (Convert.ToInt32(par) + 1).ToString().PadLeft(4,'0');
            }
        }

        public DataTable GetDocReceive(string projectCategory, string projectId, string projectName, string contractCategory, string code, string name, string id, int pageSize, int currentPageIndex, out int pageCount, out int recordCount)
        {
            DbParameter pProjectCategory = CreateParameter("ProjectCategory");
            pProjectCategory.DbType = DbType.String;
            pProjectCategory.Value = projectCategory;
            DbParameter pProject = CreateParameter("ProjectID");
            pProject.DbType = DbType.String;
            pProject.Value = projectId;
            DbParameter pProjectName = CreateParameter("Name");
            pProjectName.DbType = DbType.String;
            pProjectName.Value = projectName;
            DbParameter pCategory = CreateParameter("ContractCategory");
            pCategory.DbType = DbType.String;
            pCategory.Value = contractCategory;
            DbParameter pCode = CreateParameter("Code");
            pCode.DbType = DbType.String;
            pCode.Value = code;
            DbParameter pName = CreateParameter("Name");
            pName.DbType = DbType.String;
            pName.Value = name;
            DbParameter pID = CreateParameter("ContractID");
            pID.DbType = DbType.String;
            pID.Value = id;
            string sql = string.Format(@"Select A.ID,A.Code,A.ContractID,A.Amount,A.FromCompany,A.FromEmployee,A.ToCompany,A.ToEmployeeName,A.ReceiveDate,A.CreateTime,A.Creator,B.Name ContractName,B.Code ContractCode,C.SortCode ProjectSortCode,C.Name ProjectName,
                    D.CompName FromCompanyName,E.CompName ToCompanyName,F.IsReceive IsRec
                    From Bu_DocReceive A 
                    Inner Join BD_Contract B On A.ContractID=B.ID
                    Inner Join BD_Project C On B.ProjectID=C.ID
                    Inner Join Org_Company D On A.FromCompany=D.ID
                    Inner Join Org_Company E On A.ToCompany=E.ID
                    Left Join Bu_BatchRec F On A.ID=F.RecID
                Where B.IsInit=1 {0} {1} {2} {3} {4} {5} {6}",
                                                         string.IsNullOrEmpty(contractCategory) ? "" : string.Format(" AND B.ContractCategory={0}", pCategory.ParameterName),
                                                          string.IsNullOrEmpty(code) ? "" : string.Format(" AND B.Code LIKE '%'+{0}+ '%' ", pCode.ParameterName),
                                                          string.IsNullOrEmpty(name) ? "" : string.Format(" AND B.Name LIKE '%'+{0}+ '%' ", pName.ParameterName),
                                                          string.IsNullOrEmpty(projectCategory) ? "" : string.Format(" AND C.ProjectCategory={0}", pProjectCategory.ParameterName),
                                                          string.IsNullOrEmpty(projectId) ? "" : string.Format(" AND B.ProjectID={0}", pProject.ParameterName),
                                                          string.IsNullOrEmpty(projectName) ? "" : string.Format(" And C.Name LIKE '%'+{0}+ '%' ", pProjectName.ParameterName),
                                                          string.IsNullOrEmpty(id) ? "" : string.Format(" And A.ContractID={0}", pID.ParameterName));
            DbCommand command = GetDbCommand(sql);
            if (!string.IsNullOrEmpty(contractCategory)) { command.Parameters.Add(pCategory); }
            if (!string.IsNullOrEmpty(code)) { command.Parameters.Add(pCode); }
            if (!string.IsNullOrEmpty(name)) { command.Parameters.Add(pName); }
            if (!string.IsNullOrEmpty(projectCategory)) { command.Parameters.Add(pProjectCategory); }
            if (!string.IsNullOrEmpty(projectId)) { command.Parameters.Add(pProject); }
            if (!string.IsNullOrEmpty(projectName)) { command.Parameters.Add(pProjectName); }
            if (!string.IsNullOrEmpty(id)) { command.Parameters.Add(pID); }
            return ExecuteDataTable(command, "ProjectSortCode,ContractCode,CreateTime Desc", pageSize, currentPageIndex, out pageCount, out recordCount);

        }

        public DataTable GetCanSelectDocReceive(string[] arrFilterContractId,  string projectName, string contractCategory, string code, string name)
        {
            
            DbParameter pProjectName = CreateParameter("ProjectName");
            pProjectName.DbType = DbType.String;
            pProjectName.Value = projectName;
            DbParameter pCategory = CreateParameter("ContractCategory");
            pCategory.DbType = DbType.String;
            pCategory.Value = contractCategory;
            DbParameter pCode = CreateParameter("Code");
            pCode.DbType = DbType.String;
            pCode.Value = code;
            DbParameter pName = CreateParameter("Name");
            pName.DbType = DbType.String;
            pName.Value = name;

            List<DbParameter> lsPFilter = null;
            if (arrFilterContractId != null)
            {
                lsPFilter = GetDbParametersList("ContractID", arrFilterContractId);
            }
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"Select A.ID,A.Code,A.ContractID,A.Amount,A.FromCompany,A.FromEmployee,A.ToCompany,A.ToEmployeeName,A.ReceiveDate,A.CreateTime,A.Creator,B.Name ContractName,B.Code ContractCode,C.SortCode ProjectSortCode,
                    D.CompName FromCompanyName,E.CompName ToCompanyName,C.Name ProjectName,C.SN ProjectSN,C.Code ProjectCode
                    From Bu_DocReceive A 
                    Inner Join BD_Contract B On A.ContractID=B.ID
                    Inner Join BD_Project C On B.ProjectID=C.ID
                    Inner Join Org_Company D On A.FromCompany=D.ID
                    Inner Join Org_Company E On A.ToCompany=E.ID
                Where A.ID Not in (Select RecID From Bu_BatchRec) {0} {1} {2} {3} ",
                                                         string.IsNullOrEmpty(contractCategory) ? "" : string.Format(" AND B.ContractCategory={0}", pCategory.ParameterName),
                                                          string.IsNullOrEmpty(code) ? "" : string.Format(" AND B.Code LIKE '%'+{0}+ '%' ", pCode.ParameterName),
                                                          string.IsNullOrEmpty(name) ? "" : string.Format(" AND B.Name LIKE '%'+{0}+ '%' ", pName.ParameterName),
                                                         string.IsNullOrEmpty(projectName) ? "" : string.Format(" And C.Name LIKE '%'+{0}+ '%' ", pProjectName.ParameterName));
            if (lsPFilter != null && lsPFilter.Count > 0)
            {
                if (lsPFilter.Count == 1)
                {
                    sb.AppendFormat(" And A.ContractID<>{0}", lsPFilter[0].ParameterName);
                }
                else
                {
                    sb.Append(" A.ContractID No IN (");
                    foreach (DbParameter p in lsPFilter)
                    {
                        sb.Append(p.ParameterName);
                        sb.Append(",");
                    }
                    sb.Remove(sb.Length - 1, 1);
                    sb.Append(")");
                }
            }
            sb.Append(" Order by A.Code");
            DbCommand command = GetDbCommand(sb.ToString());
            if (lsPFilter != null && lsPFilter.Count > 0)
            {
                foreach (DbParameter p in lsPFilter)
                {
                    command.Parameters.Add(p);
                }
            }
            if (!string.IsNullOrEmpty(contractCategory)) { command.Parameters.Add(pCategory); }
            if (!string.IsNullOrEmpty(code)) { command.Parameters.Add(pCode); }
            if (!string.IsNullOrEmpty(name)) { command.Parameters.Add(pName); }
            if (!string.IsNullOrEmpty(projectName)) { command.Parameters.Add(pProjectName); }
            return ExecuteDataTable(command);

        }

        /// <summary>
        /// 按合同统计申请笔数及申请金额
        /// </summary>
        /// <param name="contractId"></param>
        /// <returns></returns>
        public DataTable GetSumAmountDocRecByContract(string contractId)
        {
            DbParameter pContractID=GetDbParameter("ContractID",contractId);
            string sql = string.Format(@"Select Count(ID) count_ID,Sum(Amount) Sum_Amount
                    From Bu_DocReceive
                    Where ContractID={0}", pContractID.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pContractID);
            return ExecuteDataTable(command);
        }

        /// <summary>
        /// 按合同统计初审笔数及初审金额
        /// </summary>
        /// <param name="contractId"></param>
        /// <returns></returns>
        public DataTable GetSumAmount_S_ByContract(string contractId)
        {
            DbParameter pContractID=GetDbParameter("ContractID",contractId);
            string sql = string.Format(@"SELECT COUNT(ID) AS count_ID,SUM(Amount_S) as Sum_Amount
                FROM Bu_AuditTable  Where ContractID={0}", pContractID.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pContractID);
            return ExecuteDataTable(command);
        }
    }
}
